﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Data.SQLite;

namespace MicroDal.Providers
{
    public class SQLiteResource : IDbResource
    {
        private SQLiteConnection _connection;
        public SQLiteResource(string connectionString)
        {
            _connection = new SQLiteConnection(connectionString);
        }

        public object ExecuteScalar(string sql, CommandType commandType, IEnumerable<DbParameter> parameters)
        {
            using (var command = new SQLiteCommand(sql, _connection, tx))
            {
                command.CommandType = commandType;
                foreach (SQLiteParameter parameter in parameters)
                    command.Parameters.Add(parameter);

                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                return command.ExecuteScalar();
            }
        }

        public int Execute(string sql, CommandType commandType, IEnumerable<DbParameter> parameters)
        {
            if (sql.Equals("backup", StringComparison.OrdinalIgnoreCase))
            {
                _connection.Open();
                var con = new SQLiteConnection(string.Format(@"Data Source=|DataDirectory|\{0}_backup.db3;Pooling=true;FailIfMissing=false", _connection.DataSource));
                con.Open();
                _connection.BackupDatabase(con, "main", "main", -1, null, -1);
                con.Close();
                _connection.Clone();
                return 0;
            }
            using (var command = new SQLiteCommand(sql, _connection, tx))
            {
                command.CommandType = commandType;
                foreach (SQLiteParameter parameter in parameters)
                    command.Parameters.Add(parameter);

                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                return command.ExecuteNonQuery();
            }
        }

        public IEnumerable<T> Fetch<T>(string sql, CommandType commandType, IEnumerable<DbParameter> parameters, Func<DbDataReader, T> mapper)
        {
            using (var command = new SQLiteCommand(sql, _connection, tx))
            {
                command.CommandType = commandType;
                foreach (SQLiteParameter parameter in parameters)
                    command.Parameters.Add(parameter);

                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                SQLiteDataReader reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    reader.Close();
                    yield break;
                }

                while (reader.Read())
                {
                    yield return mapper(reader);
                }
                reader.Close();
            }
        }

        public T FetchOne<T>(string sql, CommandType commandType, IEnumerable<DbParameter> parameters, Func<DbDataReader, T> mapper)
        {

            using (var command = new SQLiteCommand(sql, _connection, tx))
            {
                command.CommandType = commandType;
                foreach (SQLiteParameter parameter in parameters)
                    command.Parameters.Add(parameter);

                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                Debug.Assert(reader != null);

                T result = reader.Read() ? mapper(reader) : default(T);

                reader.Close();
                return result;
            }
        }

        public T GetLastInsertedId<T>()
        {
            using (var command = new SQLiteCommand("SELECT last_insert_rowid()", _connection, tx))
            {
                if (_connection.State != ConnectionState.Open)
                    return default(T);
                return (T)command.ExecuteScalar();
            }
        }

        public Func<ParameterConfig, object, DbParameter> ParameterInitializer
        {
            get
            {
                return (config, value) => new SQLiteParameter(config.Name, value) { Direction = config.Direction };
            }
        }

        // TODO: Analyze rules of using ? and @ as parametername
        public string ParameterPattern { get { return @"[^@]?(?<name>@\w+)"; } }

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
        }

        #endregion

        private SQLiteTransaction tx;
        public DbTransaction GetTransaction(IsolationLevel isolationLevel)
        {
            if (_connection.State != ConnectionState.Open)
                _connection.Open();

            if (tx == null)
                tx = _connection.BeginTransaction(isolationLevel);
            return tx;
        }
    }
}
